package gen.tools.reports;

import gen.tools.Widgets;
import gen.tools.deploy.QDPS;
import gen.tools.deployByDelivery.FileStr;
import gen.tools.structures.Req;
import gen.tools.structures.S5;
import gen.tools.structures.SquadParis;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.Date;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFPalette;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellRangeAddress;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.IndexedColors;

public class GenQueryParisReport extends HttpServlet {
	private static final long serialVersionUID = 1L;
       
    public GenQueryParisReport() {
        super();
    }

	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		doPost(request,response);
	}

	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		
		final Widgets wid = new Widgets();
		request.setCharacterEncoding(wid.UIcodeSet);
		response.setCharacterEncoding(wid.UIcodeSet);
		response.setContentType("application/octet-stream; charset=iso-8859-1");
		response.setHeader("Content-disposition","attachment; filename=ParisReport.xls");
		

		String tmpFileName = System.currentTimeMillis() + ".xls";
		String WebDirPath = request.getSession().getServletContext().getRealPath("/");
		final String ENG_FONT_STYLE = "Verdana";
		final int IP_FILE_WIDTH = 5700;
		final int VER_WIDTH = 1400;
		final int FILESIZE_WIDTH = 6300;
		final int columnNum = 6;
		int cur = 6;	//目前使用到的row
		
		int localSystemId = Integer.parseInt( request.getParameter("localSystemId") );	//本機系統的系統ID
		
		FileInputStream fis = null;
		FileOutputStream fos = null;
		OutputStream out = null;
		
		final HSSFWorkbook wb = new HSSFWorkbook();
		final HSSFPalette palette = wb.getCustomPalette();
		palette.setColorAtIndex(HSSFColor.LAVENDER.index, (byte) 91, (byte) 155, (byte) 213);	//借用 LAVENDAR來做顏色的 override
		palette.setColorAtIndex(HSSFColor.BROWN.index, (byte) 155, (byte) 194, (byte) 230);		//借用 BROWN來做顏色的 override
		palette.setColorAtIndex(HSSFColor.GREY_50_PERCENT.index, (byte) 221, (byte) 235, (byte) 247);		//借用 GREY_50_PERCENT來做顏色的 override
		final HSSFColor headerColor = palette.getColor(HSSFColor.LAVENDER.index);
		final HSSFColor squadColor = palette.getColor(HSSFColor.BROWN.index);
		final HSSFColor actColor = palette.getColor(HSSFColor.GREY_50_PERCENT.index);
		final DataFormat format = wb.createDataFormat();
		
		//給單頭，且是數字用
		final HSSFCellStyle STYLE_BH_NUM = wb.createCellStyle();
		STYLE_BH_NUM.setDataFormat(format.getFormat("#,##0"));
		STYLE_BH_NUM.setAlignment( HSSFCellStyle.ALIGN_RIGHT );
		STYLE_BH_NUM.setVerticalAlignment( CellStyle.VERTICAL_CENTER );
		STYLE_BH_NUM.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM);
		
		//給單頭，置中
		final HSSFCellStyle STYLE_BH_CENTER = wb.createCellStyle();
		STYLE_BH_CENTER.setVerticalAlignment( CellStyle.VERTICAL_CENTER );
		STYLE_BH_CENTER.setAlignment( HSSFCellStyle.ALIGN_CENTER );
		STYLE_BH_CENTER.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM);
		
		//給單頭，需求
		final HSSFCellStyle STYLE_BH_REQUIRE = wb.createCellStyle();
		STYLE_BH_REQUIRE.setVerticalAlignment( CellStyle.VERTICAL_CENTER );
		STYLE_BH_REQUIRE.setAlignment( HSSFCellStyle.ALIGN_LEFT );
		STYLE_BH_REQUIRE.setWrapText(true);
		STYLE_BH_REQUIRE.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM);
		
		
		//給版次、檔案大小用
		final HSSFCellStyle style_Num = wb.createCellStyle();
		style_Num.setDataFormat(format.getFormat("#,##0"));
		style_Num.setVerticalAlignment( CellStyle.VERTICAL_CENTER );
		
		//給 檔名用
		final HSSFCellStyle STYLE_STRING = wb.createCellStyle();
		STYLE_STRING.setVerticalAlignment( CellStyle.VERTICAL_CENTER );
		
		// 設定字型1 英文字，白色
		final HSSFFont font1 = wb.createFont();
		font1.setFontName("微軟正黑體");
		font1.setColor(IndexedColors.WHITE.getIndex());
		font1.setFontHeightInPoints((short) 12);
		
		final HSSFFont font2 = wb.createFont();
		font2.setFontName(ENG_FONT_STYLE);
		font2.setColor(IndexedColors.BLACK.getIndex());
		font2.setFontHeightInPoints((short) 12);

		final HSSFFont font3 = wb.createFont();
		font3.setFontName("微軟正黑體");
		font3.setColor(IndexedColors.BLACK.getIndex());
		font3.setFontHeightInPoints((short) 12);
		
		final CellStyle csvs = wb.createCellStyle();
		csvs.setVerticalAlignment( CellStyle.VERTICAL_CENTER );
		csvs.setFont(font3);
		// 設定cell style的變數 cs1， 置中、字型1
		final CellStyle cs1 = wb.createCellStyle();
		cs1.setAlignment( HSSFCellStyle.ALIGN_CENTER );
		cs1.setVerticalAlignment( CellStyle.VERTICAL_CENTER );
		cs1.setFont(font1);
		cs1.setFillForegroundColor( headerColor.getIndex() );
		cs1.setFillPattern(CellStyle.SOLID_FOREGROUND);
		
		final CellStyle csABC = wb.createCellStyle();
		csABC.setAlignment( HSSFCellStyle.ALIGN_RIGHT );
		csABC.setVerticalAlignment( CellStyle.VERTICAL_CENTER );
		csABC.setFont(font2);
		csABC.setFillForegroundColor( actColor.getIndex() );
		csABC.setFillPattern(CellStyle.SOLID_FOREGROUND);
		
		
		final CellStyle cs2 = wb.createCellStyle();
		cs2.setAlignment( HSSFCellStyle.ALIGN_RIGHT );
		cs2.setVerticalAlignment( CellStyle.VERTICAL_CENTER );
		cs2.setFillForegroundColor( squadColor.getIndex() );
		cs2.setFillPattern(CellStyle.SOLID_FOREGROUND);
		cs2.setFont(font2);
		
		final CellStyle cs3 = wb.createCellStyle();
		cs3.setAlignment( HSSFCellStyle.ALIGN_LEFT );
		cs3.setVerticalAlignment( CellStyle.VERTICAL_CENTER );
		cs3.setFillForegroundColor( actColor.getIndex() );		//設定顏色
		cs3.setFillPattern(CellStyle.SOLID_FOREGROUND);			//設定填滿
		cs3.setFont(font3);
		
		final HSSFSheet fs = wb.createSheet("查詢結果");
		
		fs.createRow(0).createCell(0).setCellValue(new HSSFRichTextString("製表日期："
				+ wid.changeTimeFormat(new Date())));

		final HSSFRow r5 = fs.createRow(5);

		final HSSFCell cA5 = r5.createCell(0);
		cA5.setCellValue("上線序號");
		cA5.setCellStyle(cs1); // 讓此儲存格擁有此種style
		fs.setColumnWidth(cA5.getColumnIndex(), 2900 );

		final HSSFCell cB5 = r5.createCell(1);
		cB5.setCellValue("交付序號");
		cB5.setCellStyle(cs1); // 讓此儲存格擁有此種style
		fs.setColumnWidth(cB5.getColumnIndex(), 2900 );
		
		final HSSFCell cC5 = r5.createCell(2);
		cC5.setCellValue("緊急上線");
		cC5.setCellStyle(cs1); // 讓此儲存格擁有此種style
		fs.setColumnWidth(cC5.getColumnIndex(), 2900 );

		final HSSFCell cD5 = r5.createCell(3);
		cD5.setCellValue("部署環境");
		cD5.setCellStyle(cs1); // 讓此儲存格擁有此種style
		fs.setColumnWidth(cD5.getColumnIndex(), 4100 );

		final HSSFCell cE5 = r5.createCell(4);
		cE5.setCellValue("執行帳號");
		cE5.setCellStyle(cs1); // 讓此儲存格擁有此種style
		fs.setColumnWidth(cE5.getColumnIndex(), 3157 );
		
		final HSSFCell cF5 = r5.createCell(5);
		cF5.setCellValue("執行時間");
		cF5.setCellStyle(cs1); // 讓此儲存格擁有此種style
		fs.setColumnWidth(cF5.getColumnIndex(), 5030 );
		
		final HSSFCell cG5 = r5.createCell(6);
		cG5.setCellValue("相關需求");
		cG5.setCellStyle(cs1); // 讓此儲存格擁有此種style
		fs.setColumnWidth(cG5.getColumnIndex(), 10572 );
		
		final HSSFCell cH5 = r5.createCell(7);
		cH5.setCellValue("");
		cH5.setCellStyle(cs1); // 讓此儲存格擁有此種style
		fs.setColumnWidth(cH5.getColumnIndex(), 4000 );
		
		final HSSFCell cI5 = r5.createCell(8);						//IP, 檔案大小
		cH5.setCellValue("");
		cH5.setCellStyle(cs1); // 讓此儲存格擁有此種style
		fs.setColumnWidth(cI5.getColumnIndex(), IP_FILE_WIDTH );
		
		final HSSFCell cJ5 = r5.createCell(9);						//版次
		cH5.setCellValue("");
		cH5.setCellStyle(cs1); // 讓此儲存格擁有此種style
		fs.setColumnWidth(cJ5.getColumnIndex(), VER_WIDTH );
		
		final HSSFCell cK5 = r5.createCell(10);						//檔案大小
		cH5.setCellValue("");
		cH5.setCellStyle(cs1); // 讓此儲存格擁有此種style
		fs.setColumnWidth(cK5.getColumnIndex(), FILESIZE_WIDTH );
		
		final HSSFCell cL5 = r5.createCell(11);						//下線, 部署
		cH5.setCellValue("");
		cH5.setCellStyle(cs1); // 讓此儲存格擁有此種style
		fs.setColumnWidth(cL5.getColumnIndex(), VER_WIDTH );
		
		fs.addMergedRegion(new CellRangeAddress(0, 0, 0, 3));	//製表日期的欄位
		fs.addMergedRegion(new CellRangeAddress(cur-1, cur-1, columnNum+1, columnNum+7));	//表頭
		/******	以上是Table的表頭，以下是表身	******/
		
		try {
			final ArrayList<QDPS> qdps = wid.getQDR4Paris(localSystemId);
			
			final int tailCell = columnNum+1;
			
			for(int i=0; i < qdps.size(); i++){
				final QDPS qdp = qdps.get(i);

				//上線序號
				fs.createRow(cur).createCell(0).setCellValue( qdp.getDepmainSN() );								
				fs.getRow(cur).getCell(0).setCellStyle( STYLE_BH_NUM );
				//交付序號
				fs.getRow(cur).createCell(1).setCellValue( qdp.getDeliverySN() );								
				fs.getRow(cur).getCell(1).setCellStyle( STYLE_BH_NUM );
				
				fs.getRow(cur).createCell(2).setCellValue( qdp.isUrgent()?"是":"否" );					//緊急上線
				fs.getRow(cur).getCell(2).setCellStyle(STYLE_BH_CENTER);
				
				fs.getRow(cur).createCell(3).setCellValue( qdp.getEnvName()+" ("+qdp.getEnvId()+")" );	//部署環境
				fs.getRow(cur).getCell(3).setCellStyle(STYLE_STRING);
				
				fs.getRow(cur).createCell(4).setCellValue( qdp.getEmpName() );							//執行人員
				fs.getRow(cur).getCell(4).setCellStyle(STYLE_STRING);
				
				fs.getRow(cur).createCell(5).setCellValue( qdp.getbDT().replace("<br>", " ") );			//執行時間
				fs.getRow(cur).getCell(5).setCellStyle(STYLE_BH_REQUIRE);
				
				String tmp ="";
				for(int j=0; j< qdp.getReqs().size(); j++) {
					final Req req = qdp.getReqs().get(j);
					if(j!=0) { tmp+="\n"; }
					tmp += (req.getReqSID()+"-"+req.getReqDesc());
				}
				fs.getRow(cur).createCell(6).setCellValue( tmp );										//相關需求
				fs.getRow(cur).getCell(6).setCellStyle(STYLE_BH_REQUIRE);
				
				for(int j=0; j<=columnNum;j++ ) {
					fs.addMergedRegion(new CellRangeAddress(cur, (cur+qdp.getRowspan()-1), j, j));	
				}
				
				int t = cur;	//記錄在報表的最後一格，現在在那一列
				for(int j=0; j<qdp.getSps().size(); j++ ) {
					
					final SquadParis sp = qdp.getSps().get(j);
					if ( j ==0 ) {
						fs.getRow(t).createCell(tailCell).setCellValue(sp.getSquadId());	//Server Group IP	
					} else {
						fs.createRow(t).createCell(tailCell).setCellValue(sp.getSquadId());	//Server Group IP
					}
					fs.getRow(t).getCell(tailCell).setCellStyle(cs2);	//設定 "SquadId" 欄位的 style
					
					fs.getRow(t).createCell(tailCell+1).setCellValue("");
					fs.addMergedRegion(new CellRangeAddress(t, t, tailCell+1, tailCell+6));
					t++;	//下一行
					fs.createRow(t).createCell(tailCell).setCellValue("a.");
					fs.getRow(t).getCell(tailCell).setCellStyle(csABC);
					
					fs.getRow(t).createCell(tailCell+1).setCellValue("關閉 HTTPD 服務");
					fs.getRow(t).getCell(tailCell+1).setCellStyle(cs3);
					
					
					fs.addMergedRegion(new CellRangeAddress(t, t, tailCell+1, tailCell+6));
					t++;	//下一行	//開始顯示欲關閉的主機
					for( int k=0; k< sp.getHts().size(); k++ ) {
						final S5 ht = sp.getHts().get(k);
						fs.createRow(t).createCell(tailCell).setCellValue("");
						fs.getRow(t).createCell(tailCell+1).setCellValue((k+1)+". "+ht.getS1());
						fs.getRow(t).createCell(tailCell+2).setCellValue("");
						fs.addMergedRegion(new CellRangeAddress(t, t, tailCell+2, tailCell+4));
						fs.getRow(t).createCell(tailCell+5).setCellValue(ht.getS2()==null?"未執行":ht.getS2());
						fs.getRow(t).createCell(tailCell+6).setCellValue(ht.getS4());
						t++;
					}
					fs.createRow(t).createCell(tailCell).setCellValue("b.");
					fs.getRow(t).getCell(tailCell).setCellStyle(csABC);
					fs.getRow(t).createCell(tailCell+1).setCellValue("檔案部署");
					fs.getRow(t).createCell(tailCell+2).setCellValue("版次");
					fs.getRow(t).createCell(tailCell+3).setCellValue("檔案大小(bytes)");
					fs.getRow(t).createCell(tailCell+4).setCellValue("");
					for(int a=1; a<5;a++) {
						fs.getRow(t).getCell(tailCell+a).setCellStyle(cs3);
					}
					
					fs.addMergedRegion(new CellRangeAddress(t, t, tailCell+4, tailCell+6));
					t++;	//下一行	//開始顯示部署的檔案
					for( int k=0; k< sp.getFiles().size(); k++ ) {
						final FileStr f = sp.getFiles().get(k);
						fs.createRow(t).createCell(tailCell).setCellValue("");
						fs.addMergedRegion(new CellRangeAddress(t, t+1, tailCell, tailCell));
						
						//檔案
						fs.getRow(t).createCell(tailCell+1).setCellStyle(STYLE_STRING);
						fs.getRow(t).getCell(tailCell+1).setCellValue((k+1)+". "+f.getFileName());
						fs.addMergedRegion(new CellRangeAddress(t, t+1, tailCell+1, tailCell+1) );
						
						//版次
						fs.getRow(t).createCell(tailCell+2).setCellStyle(style_Num);
						fs.getRow(t).getCell(tailCell+2).setCellValue(f.getLocalVer());			
						fs.addMergedRegion(new CellRangeAddress(t, t+1, tailCell+2, tailCell+2) );
						
						
						//檔案大小
						fs.getRow(t).createCell(tailCell+3).setCellStyle(style_Num);
						fs.getRow(t).getCell(tailCell+3).setCellValue( f.getFileSize() );
						
						
						fs.addMergedRegion(new CellRangeAddress(t, t+1, tailCell+3, tailCell+3));
						fs.getRow(t).createCell(tailCell+4).setCellValue("下線");
						fs.getRow(t).createCell(tailCell+5).setCellValue(f.getDepRes().getS1());
						fs.getRow(t).createCell(tailCell+6).setCellValue(f.getDepRes().getS3());
						t++;
						fs.createRow(t).createCell(tailCell+4).setCellValue("部署");
						fs.getRow(t).createCell(tailCell+5).setCellValue(f.getDepRes().getS2());
						fs.getRow(t).createCell(tailCell+6).setCellValue(f.getDepRes().getS4());
						t++;
					}
					
					fs.createRow(t).createCell(tailCell).setCellValue("c.");
					fs.getRow(t).getCell(tailCell).setCellStyle(csABC);
					
					fs.getRow(t).createCell(tailCell+1).setCellValue("重啟相關檔案");
					fs.getRow(t).getCell(tailCell+1).setCellStyle(cs3);
					
					fs.addMergedRegion(new CellRangeAddress(t, t, tailCell+1, tailCell+6));
					t++;	//下一行	//開始顯示部署的檔案
					for( int k=0; k< sp.getReloadFiles().size(); k++ ) {
						final FileStr f = sp.getReloadFiles().get(k);
						
						
						if( !"".equalsIgnoreCase( f.getDepRes().getS3() ) ) {
							fs.createRow(t).createCell(tailCell).setCellValue("");
							fs.addMergedRegion(new CellRangeAddress(t, t+1, tailCell, tailCell));
							//檔案
							fs.getRow(t).createCell(tailCell+1).setCellStyle(STYLE_STRING);
							fs.getRow(t).getCell(tailCell+1).setCellValue((int) Math.ceil((k+1)/2)+1+". "+f.getFileName());
							fs.addMergedRegion(new CellRangeAddress(t, t+1, tailCell+1, tailCell+3) );
							fs.getRow(t).createCell(tailCell+4).setCellValue("下線");
							fs.getRow(t).createCell(tailCell+5).setCellValue(f.getDepRes().getS1());
							fs.getRow(t).createCell(tailCell+6).setCellValue(f.getDepRes().getS3());
						} else {
							fs.createRow(t).createCell(tailCell+4).setCellValue("部署");
							fs.getRow(t).createCell(tailCell+5).setCellValue(f.getDepRes().getS2());
							fs.getRow(t).createCell(tailCell+6).setCellValue(f.getDepRes().getS4());
						}
						t++;
					}
					
					
					
					//開始開啟HTTP
					fs.createRow(t).createCell(tailCell).setCellValue("d.");
					fs.getRow(t).getCell(tailCell).setCellStyle(csABC);
					
					fs.getRow(t).createCell(tailCell+1).setCellValue("開啟 HTTPD 服務");
					fs.getRow(t).getCell(tailCell+1).setCellStyle(cs3);
					
					fs.addMergedRegion(new CellRangeAddress(t, t, tailCell+1, tailCell+6));
					t++;	//下一行	//開始顯示欲關閉的主機
					for( int k=0; k< sp.getHts().size(); k++ ) {
						final S5 ht = sp.getHts().get(k);
						fs.createRow(t).createCell(tailCell).setCellValue("");
						fs.getRow(t).createCell(tailCell+1).setCellValue((k+1)+". "+ht.getS1());
						fs.getRow(t).createCell(tailCell+2).setCellValue("");
						fs.addMergedRegion(new CellRangeAddress(t, t, tailCell+2, tailCell+4));
						fs.getRow(t).createCell(tailCell+5).setCellValue(ht.getS3()==null?"未執行":ht.getS3());
						fs.getRow(t).createCell(tailCell+6).setCellValue(ht.getS5());
						t++;
					}
				}
				cur = t;
			}
			
			
			/* 以上Excel內容製作完成，以下開始產生檔案 */
			
			fos = new FileOutputStream(new File(WebDirPath + tmpFileName));
			wb.write(fos);
			fos.close();
			out = response.getOutputStream();
			fis = new FileInputStream(WebDirPath + tmpFileName);
			byte[] buffer = new byte[4096];
			int length;
			while ((length = fis.read(buffer)) > 0) {
				out.write(buffer, 0, length);
			}
			fis.close();
			out.flush();
			
		} catch (Exception e) {
			System.out.println(wid.errProcess(e));
			wid.errLog(e);
		} finally {
			if ( fis !=null ) {fis.close();}
			if ( out !=null ) {out.close();}
		}
	}
}
